import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

public class PoiUtil {
	public static HSSFWorkbook getExcel(String sheetName,
			List<Map<String, String>> cloumnName, List<?> data)
			throws IOException {
		HSSFWorkbook workbook = new HSSFWorkbook();// 新建excel工作薄
		HSSFSheet sheet = workbook.createSheet(sheetName);// 新建工作薄的sheet
		HSSFRow firstRow = sheet.createRow(0);// sheet顶端新建行
		if (cloumnName != null) {
			for (int i = 0; i < cloumnName.size(); i++) {
				HSSFCell cell = firstRow.createCell(i);
				cell.setCellType(HSSFCell.CELL_TYPE_STRING);
				cell.setCellValue(cloumnName.get(i).get("cloumn"));
			}
		}
		for (int i = 0; i < data.size(); i++) {
			Object model = data.get(i);
			Class<?> clazz = data.get(i).getClass();
			Method method = null;
			Object object = null;
			HSSFRow row = sheet.createRow(i + 1);
			for (int j = 0; j < cloumnName.size(); j++) {
				try {
					method = clazz.getMethod(cloumnName.get(j).get("method"));
					object = method.invoke(model);
					HSSFCell cell = row.createCell(j);
					cell.setCellType(HSSFCell.CELL_TYPE_STRING);
					if (object == null)
						cell.setCellValue("");
					else
						cell.setCellValue(object.toString());
				} catch (NoSuchMethodException e) {
					e.printStackTrace();
				} catch (SecurityException e) {
					e.printStackTrace();
				} catch (IllegalAccessException e) {
					e.printStackTrace();
				} catch (IllegalArgumentException e) {
					e.printStackTrace();
				} catch (InvocationTargetException e) {
					e.printStackTrace();
				}
			}
		}
		// FileOutputStream fOut = new FileOutputStream("E:" + File.separator
		// + "test.xlsx");
		// workbook.write(fOut);
		// fOut.flush();
		// fOut.close();
		// System.out.println("文件生成...");
		return workbook;
	}

	public static <T> List<T> setExcel(File file,
			Class<? extends Object> clazz, List<String> methodNames,
			List<Class<?>> argTypes) throws IOException,
			InstantiationException, IllegalAccessException,
			ClassNotFoundException, NoSuchMethodException, SecurityException,
			IllegalArgumentException, InvocationTargetException {
		Method method = null;
		List<T> dataList = new ArrayList<T>();
		Workbook workbook = null;
		try {
			workbook = new XSSFWorkbook(new FileInputStream(file));
		} catch (Exception e) {
			POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(file));
			workbook = new HSSFWorkbook(fs);
		}
		Sheet sheet = workbook.getSheetAt(0);
		int rowNum = sheet.getLastRowNum();// 得到总行数
		for (int i = 1; i <= rowNum; i++) {
			Row row = sheet.getRow(i);
			row.removeCell(row.getCell(0));
			@SuppressWarnings("unchecked")
			T model = (T) clazz.newInstance();
			for (int j = 0; j < row.getPhysicalNumberOfCells(); j++) {
				method = clazz.getMethod(methodNames.get(j), argTypes.get(j));
				String value = getCellFormatValue(row.getCell((short) j + 1))
						.trim();
					method.invoke(model, value);
			}
			dataList.add(model);
		}
		return dataList;

	}

	/**
	 * 根据HSSFCell类型设置数据
	 * 
	 * @param cell
	 * @return
	 */
	@SuppressWarnings("deprecation")
	public static String getCellFormatValue(Cell cell) {
		String cellvalue = "";
		if (cell != null) {
			// 判断当前Cell的Type
			switch (cell.getCellType()) {
			// 如果当前Cell的Type为NUMERIC
			case HSSFCell.CELL_TYPE_NUMERIC:
			case HSSFCell.CELL_TYPE_FORMULA: {
				// 判断当前的cell是否为Date
				if (HSSFDateUtil.isCellDateFormatted(cell)) {
					// 如果是Date类型则，转化为Date格式

					// 方法1：这样子的date格式是带时分秒的：2011-10-12 0:00:00
					cellvalue = cell.getDateCellValue().toLocaleString();

					// 方法2：这样子的date格式是不带带时分秒的：2011-10-12
					// Date date = cell.getDateCellValue();
					// SimpleDateFormat sdf = new
					// SimpleDateFormat("yyyy-MM-dd");
					// cellvalue = sdf.format(date);
					//
				}
				// 如果是纯数字
				else {
					// 取得当前Cell的数值
					cellvalue = String.valueOf(cell.getNumericCellValue());
				}
				break;
			}
			// 如果当前Cell的Type为STRIN
			case HSSFCell.CELL_TYPE_STRING:
				// 取得当前的Cell字符串
				cellvalue = cell.getRichStringCellValue().getString();
				break;
			// 默认的Cell值
			default:
				cellvalue = " ";
			}
		} else {
			cellvalue = "";
		}
		return cellvalue;

	}

	public static void main(String[] args) {

	}
	//
	// public static void main(String[] args) throws IOException {
	// List<Map<String, String>> cloumnName = new ArrayList<Map<String,
	// String>>();
	// Map<String, String> map1 = new HashMap<String, String>();
	// map1.put("cloumn", "作者");
	// map1.put("method", "getAuthor");
	// cloumnName.add(map1);
	// Map<String, String> map2 = new HashMap<String, String>();
	// map2.put("cloumn", "书名");
	// map2.put("method", "getName");
	// cloumnName.add(map2);
	// Map<String, String> map3 = new HashMap<String, String>();
	// map3.put("cloumn", "出版社");
	// map3.put("method", "getPublisher");
	// cloumnName.add(map3);
	// Map<String, String> map4 = new HashMap<String, String>();
	// map4.put("cloumn", "排序");
	// map4.put("method", "getSort");
	// cloumnName.add(map4);
	// Map<String, String> map5 = new HashMap<String, String>();
	// map5.put("cloumn", "Id");
	// map5.put("method", "getId");
	// cloumnName.add(map5);
	// Map<String, String> map6 = new HashMap<String, String>();
	// map6.put("cloumn", "出版日期");
	// map6.put("method", "getPublishDate");
	// cloumnName.add(map6);
	// List<Object> books = new ArrayList<Object>();
	// for (int i = 0; i < 5; i++) {
	// Book book = new Book();
	// book.setAuthor("张" + i);
	// book.setName("掏粪" + i);
	// book.setSort(i);
	// books.add(book);
	// PoiUtil.getExcel("ss", cloumnName, books);
	// }
	// }
	// public static void main(String[] args) throws InstantiationException,
	// IllegalAccessException, ClassNotFoundException,
	// NoSuchMethodException, SecurityException, IllegalArgumentException,
	// InvocationTargetException, IOException {
	// File file = new File("E:" + File.separator + "phone.xls");
	// List<String> methodNames = new ArrayList<String>();
	// List<Class<?>> argTypes = new ArrayList<>();
	// argTypes.add(String.class);
	// argTypes.add(String.class);
	// argTypes.add(Date.class);
	// argTypes.add(Date.class);
	// methodNames.add("setPhone");
	// methodNames.add("setLabel");
	// methodNames.add("setCreateTime");
	// methodNames.add("setUpdateTime");
	// List<BlackList> data = PoiUtil.setExcel(file, BlackList.class,
	// methodNames, argTypes);
	// }
}
